package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BatchTransactionDemo {
    static final String URL = "jdbc:mysql://localhost:3306/school";
    static final String USER = "root";
    static final String PASS = "123456";
    static final int BATCH_SIZE = 5000; // 最佳实践：500-5000

    public static void main(String[] args) {
        // 准备测试数据
        List<Student> students = generateTestData(100_000);

        // 场景1：普通逐条插入（无批处理无事务）
        testWithoutBatchAndTransaction(students);

        // 场景2：仅使用批处理
        testWithBatchOnly(students);

        // 场景3：仅使用事务
        testWithTransactionOnly(students);

        // 场景4：批处理+事务（最佳实践）
        testWithBatchAndTransaction(students);
    }

    // 生成测试数据
    private static List<Student> generateTestData(int count) {
        List<Student> list = new ArrayList<>();
        for (int i = 1; i <= count; i++) {
            list.add(new Student("Student_" + i, 18 + i % 5));
        }
        return list;
    }

    // 场景1：普通逐条插入
    private static void testWithoutBatchAndTransaction(List<Student> students) {
        long start = System.currentTimeMillis();
        String sql = "INSERT INTO students(name, age) VALUES(?, ?)";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            for (Student s : students) {
                pstmt.setString(1, s.name);
                pstmt.setInt(2, s.age);
                pstmt.executeUpdate(); // 每次执行都产生网络往返
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.printf("[普通插入] 耗时：%.2fs%n", (System.currentTimeMillis() - start)/1000.0);
    }

    // 场景2：仅使用批处理
    private static void testWithBatchOnly(List<Student> students) {
        long start = System.currentTimeMillis();
        String sql = "INSERT INTO students(name, age) VALUES(?, ?)";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < students.size(); i++) {
                Student s = students.get(i);
                pstmt.setString(1, s.name);
                pstmt.setInt(2, s.age);
                pstmt.addBatch();

                // 分批次提交
                if ((i + 1) % BATCH_SIZE == 0 || i == students.size() - 1) {
                    pstmt.executeBatch();
                    pstmt.clearBatch();
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.printf("[仅批处理] 耗时：%.2fs%n", (System.currentTimeMillis() - start)/1000.0);
    }

    // 场景3：仅使用事务
    private static void testWithTransactionOnly(List<Student> students) {
        long start = System.currentTimeMillis();
        String sql = "INSERT INTO students(name, age) VALUES(?, ?)";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
            conn.setAutoCommit(false); // 关闭自动提交

            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                for (Student s : students) {
                    pstmt.setString(1, s.name);
                    pstmt.setInt(2, s.age);
                    pstmt.executeUpdate();
                }
                conn.commit(); // 统一提交
            } catch (SQLException e) {
                conn.rollback(); // 出错回滚
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.printf("[仅事务] 耗时：%.2fs%n", (System.currentTimeMillis() - start)/1000.0);
    }

    // 场景4：批处理+事务（推荐方式）
    private static void testWithBatchAndTransaction(List<Student> students) {
        long start = System.currentTimeMillis();
        String sql = "INSERT INTO students(name, age) VALUES(?, ?)";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
            conn.setAutoCommit(false);

            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                for (int i = 0; i < students.size(); i++) {
                    Student s = students.get(i);
                    pstmt.setString(1, s.name);
                    pstmt.setInt(2, s.age);
                    pstmt.addBatch();
                    /*
                    如果当前学生索引加1后能被BATCH_SIZE整除，说明当前批次已满，需要执行批处理。
如果当前学生是列表中的最后一个，也需要执行批处理以确保所有数据都被插入。
                     */
                    if ((i + 1) % BATCH_SIZE == 0 || i == students.size() - 1) {
                        pstmt.executeBatch();
                        pstmt.clearBatch();
                    }
                }
                conn.commit();
            } catch (SQLException e) {
                conn.rollback();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.printf("[批处理+事务] 耗时：%.2fs%n", (System.currentTimeMillis() - start)/1000.0);
    }

    static class Student {
        String name;
        int age;

        public Student(String name, int age) {
            this.name = name;
            this.age = age;
        }
    }
}
